package Server.Database;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Time;
import java.util.Vector;

import Server.Medida;
import Server.Sensor;

public class Conexion {

	private static Connection connection;
	private static ResultSet resultSet;
	private static Statement statement;
	
	public Conexion(){
		
		 connection = null;  
	     resultSet = null;  
	     statement = null;  
	     
	}
	
	public static void setOnline(String usuario){
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement.executeUpdate("UPDATE usuario SET online = 'true' WHERE nombre='"+usuario+"';");  
	          
	           
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	            
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		
	}
	
	public static void setNameUser(String usuario, String newname){
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement.executeUpdate("UPDATE usuario SET nombre ='"+newname+"' WHERE nombre='"+usuario+"';");  
	          
	           
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	            
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		
	}
	
	public static void setPass(String usuario, String newpass){
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement.executeUpdate("UPDATE usuario SET pass ="+newpass+" WHERE nombre='"+usuario+"';");  
	          
	           
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	            
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		
	}
	
	public static void crearUsuario(String usuario, String pass){
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement.executeUpdate("INSERT INTO usuario (nombre, pass, admin, online) VALUES ('"+usuario+"','"+pass+"','false','false'");  
	          
	           
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	            
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		
	}
	
public static void setOffline(String usuario){
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement.executeUpdate("UPDATE usuario SET online = 'false' WHERE nombre='"+usuario+"';");  
	          
	           
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	            
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		
	}


	
	public static boolean isOnline(String usuario){
		boolean b=false;
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT online FROM usuario WHERE nombre='"+usuario+"';");  
	          
	           b=resultSet.getBoolean(1);
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return b;
	}
	
	public static void deleteUser(String usuario){
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("DELETE FROM usuario WHERE nombre='"+usuario+"';");  
	         
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
	}
	
	//Devuelve un vector de usuarios para el comando SHOWONLINE
		public static Vector<String> listaOnline(){
			
			Vector<String> u=new Vector<String>();
			
			
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT nombre FROM usuario WHERE online='true';");  
		         while (resultSet.next()) 
		         {  
		             String usuario=resultSet.getString(1);
		        	 u.add(usuario);
		         }  
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			//System.out.println(s.size());
			return u;
		}
		
		
public static Vector<Usuario> listaUsers(){
                       
                        Vector<Usuario> u=new Vector<Usuario>();
                       
                       
                        try
                     {  
                         Class.forName("org.sqlite.JDBC");  
                         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
                      //   System.out.println("conectado");
                         statement = connection.createStatement();  
                         resultSet = statement  
                                 .executeQuery("SELECT * FROM usuario;");  
                         while (resultSet.next())
                         {  
                                 Usuario user =new Usuario (resultSet.getString(1), resultSet.getString(2), resultSet.getBoolean(3), resultSet.getBoolean(4)) ;
                                 u.add(user);
                         }  
                     }
                     catch (Exception e)
                     {  
                         e.printStackTrace();  
                     }
                     finally
                     {  
                         try
                         {  
                             resultSet.close();  
                             statement.close();  
                             connection.close();
                           //  System.out.println("Conexion finalizada");
                         }
                         catch (Exception e)
                         {  
                             e.printStackTrace();  
                         }  
                     }
                        //System.out.println(s.size());
                        return u;
                }
	
	
	public static boolean isAdmin(String usuario){
boolean b=false;
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT admin FROM usuario WHERE nombre='"+usuario+"';");  
	          
	           b=resultSet.getBoolean(1);
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return b;
	}
	
	//Devuelve true si el nombre esta registrado
	//Devuelve false si el nombre no esta registrado
	
	public static void insertarUsuario(String user,String pass,boolean admin,boolean online){
		PreparedStatement ps=null;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         
	         ps=connection.prepareStatement("INSERT INTO usuario VALUES(?,?,?,?);");
	         ps.setString(1, user);
	         ps.setString(2, pass);
	         ps.setBoolean(3, false);
	         ps.setBoolean(4, false);
	         ps.execute();
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	              
	           ps.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     }
		
		/*try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement  
	                 .executeQuery("INSERT INTO usuario (nombre,clave) VALUES('"+user+"','"+pass+"')");  
	     statement.ex
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	              
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } */
	}
	
	public static void insertarSensor(String descripcion,boolean estado,int id_vehiculo){
		PreparedStatement ps=null;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         
	         ps=connection.prepareStatement("INSERT INTO sensor (descripcion,estado,id_vehiculo) VALUES(?,?,?);");
	         ps.setString(1, descripcion);
	         ps.setBoolean(2, estado);
	         ps.setInt(3, id_vehiculo);
	         ps.execute();
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	              
	           ps.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     }
		
	}
	
	public static void insertarVehiculo(boolean estado_gps,String url){
		PreparedStatement ps=null;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         
	         ps=connection.prepareStatement("INSERT INTO vehiculo (estado_gps,url) VALUES(?,?);");
	        
	         ps.setBoolean(1, estado_gps);
	         ps.setString(2, url);
	         ps.execute();
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	              
	           ps.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     }
		
	}
	
	public static void insertarCoordenada(String latitud,String longitud){
		PreparedStatement ps=null;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         
	         ps=connection.prepareStatement("INSERT INTO coordenada (latitud,longitud) VALUES(?,?);");
	        
	         ps.setString(1, latitud);
	         ps.setString(2, longitud);
	         ps.execute();
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	              
	           ps.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     }
		
	}
	
	public static void insertarMedida(int id_sensor,Date fecha,Time hora,int id_coord,float valor){
		PreparedStatement ps=null;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         
	         ps=connection.prepareStatement("INSERT INTO medida (id_sensor,fecha,hora,id_coord,valor) VALUES(?,?,?,?,?);");
	         ps.setInt(1, id_sensor);
	         ps.setDate(2, fecha);
	         ps.setTime(3, hora);
	         ps.setInt(4, id_coord);
	         ps.setFloat(5, valor);
	         ps.execute();
	         
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	              
	           ps.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     }
		
	}
	
	public static void setAdminTrue(String login){
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         statement  
	                 .executeUpdate("UPDATE usuario SET admin='true' WHERE nombre='"+login+"';");  
	          //System.out.println("rs="+resultSet.getString(1));
	          
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	               
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
	}
	
	
	public static String user(String login){
		String s="";
		//System.out.println("login:"+login);
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT nombre FROM usuario WHERE nombre='"+login+"';");  
	          //System.out.println("rs="+resultSet.getString(1));
	           if(resultSet.getString(1).equals(login)){
	        	   s=login;
	           }else{
	        	   s="";
	           }
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		System.out.println("s:"+s);
		return s;
		
	}
	
	//Devuelve true si el pass es correcto
	//Devuelve false si el pass es incorrecto
	public static boolean pass(String login,String pass){
		boolean b=false;
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT clave FROM usuario WHERE nombre='"+login+"';");  
	          
	           if(resultSet.getString(1).equals(pass)){
	        	   b=true;
	           }else{
	        	   b=false;
	           }
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return b;
	}
	
	//Devuelve un vector de sensores para el comando LISTSENSOR
	public static Vector<Sensor> listaSensores(){
		
		Vector<Sensor> s=new Vector<Sensor>();
		
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT * FROM sensor;");  
	         while (resultSet.next()) 
	         {  
	             Sensor sensor=new Sensor(resultSet.getInt(1),resultSet.getString(2),resultSet.getBoolean(3));
	        	 s.add(sensor);
	         }  
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		//System.out.println(s.size());
		return s;
	}
	
	//Devuelve un vector de medidas para el comando HISTORICO
	public static Vector<Medida> listaMedidas(int id){
		Vector<Medida> v=new Vector<Medida>();
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT * FROM medida WHERE id_sensor="+id+";");  
	         while (resultSet.next()) 
	         {  
	            Medida m=new Medida(resultSet.getDate(3),resultSet.getTime(4),"","",resultSet.getFloat(6));
	        	int id_coord=resultSet.getInt(5);
	            Statement st2=connection.createStatement();
	        	ResultSet rs2=st2.executeQuery("SELECT * FROM coordenada WHERE id_coord="+id_coord+";");
	        	while(rs2.next()){
	        		String latitud=rs2.getString(2);
	        		String longitud=rs2.getString(3);
	        		m.setCoordenadas(latitud, longitud);
	        	}
	        	rs2.close();
	        	st2.close();
	            v.add(m);
	         }  
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	        	 
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return v;
	}
	
	//Cambia el estado del sensor a ON si su estado previo es OFF
	//Devolvera 1 si se ha cambiado correctamente a ON
	//Devolvera 0 si su estado previo era ON
	//Devolvera -1 si el sensor no existe
	public static int setSensorON(int id_sensor){
		int i=2;
		boolean b=false;
		PreparedStatement ps=null;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT estado FROM sensor WHERE id_sensor="+id_sensor+";");  
	         if(!resultSet.next()){
		        	i=-1;
		        }else{
	          
	            b=resultSet.getBoolean(1);
	         System.out.println(b);
		        
	         if(b==false){
	        	 i=1;
	        	 ps=connection.prepareStatement("UPDATE sensor SET estado = ? WHERE id_sensor="+id_sensor+";");
	        	ps.setBoolean(1, true);
	        	 ps.executeUpdate();
	        	 //statement.executeUpdate("UPDATE sensor SET estado = 'true' WHERE id_sensor="+id_sensor+";");  
	           System.out.println("UPDATED");
	         }else{
	        	 i=0;
	         }
		        }
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  

	        	 resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return i;
	}
	
	    //Cambia el estado del sensor a OFF si su estado previo es ON
		//Devolvera 1 si se ha cambiado correctamente a OFF
		//Devolvera 0 si su estado previo era OFF
	    //Devolvera -1 si el sensor no existe
		public static int setSensorOFF(int id_sensor){
			int i=2;
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado FROM sensor WHERE id_sensor="+id_sensor+";");  
		         if(!resultSet.next()){
			        	i=-1;
			        }else{
		          
		            b=resultSet.getBoolean(1);
		         
			        
		         if(b==true){
		        	 i=1;
		        	 statement.executeUpdate("UPDATE sensor SET estado = 'false' WHERE id_sensor="+id_sensor+";");  
		         }else{
		        	 i=0;
		         }
			        }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return i;
		}
		
		 //Cambia el estado del GPS a ON si su estado previo es OFF
		//Devolvera true si se ha cambiado correctamente a ON
		//Devolvera false si su estado previo era ON
		public static boolean setGPSON(String iD_Vehiculo){
			PreparedStatement ps=null;
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado_gps FROM vehiculo WHERE id_vehiculo="+Integer.parseInt(iD_Vehiculo.substring(9))+";");  
		          
		            b=resultSet.getBoolean(1);
		           
		         if(b==false){
		        	ps=connection.prepareStatement("UPDATE vehiculo SET estado_gps = ? WHERE id_vehiculo="+Integer.parseInt(iD_Vehiculo.substring(9))+";");
		        	ps.setBoolean(1, true);
		        	ps.executeUpdate();
		        	// statement.executeUpdate("UPDATE vehiculo SET estado_gps = 'true' WHERE id_vehiculo="+Integer.parseInt(iD_Vehiculo.substring(9))+";");  
		        // System.out.println("UPDATED");
		         b=true;
		         }else{
		        	 b=false;
		         }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return b;
		}
	
		 //Cambia el estado del sensor a OFF si su estado previo es ON
		//Devolvera true si se ha cambiado correctamente a OFF
		//Devolvera false si su estado previo era OFF
		public static boolean setGPSOFF(String iD_Vehiculo){
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado_gps FROM vehiculo WHERE id_vehiculo="+Integer.parseInt(iD_Vehiculo.substring(9))+";");  
		        
		            b=resultSet.getBoolean(1);
	  
		         if(b==true){
		        	 statement.executeUpdate("UPDATE vehiculo SET estado_gps = 'false' WHERE id_vehiculo="+Integer.parseInt(iD_Vehiculo.substring(9))+";");  
		             
		         }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return b;
		}
		
		//Devolvera la lista de atributos para el comando GET_VALACT is_sensor
		//Devolvera el objeto Medida correspondiente si el comando se ha ejecutado correctamente
		//Devolvera 0 si el sensor esta a off
		//Devolvera -1 si no existe el sensor introducido
		public static Object getValact(int id_sensor){
			Object o=null;
			int i=-1;
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT id_sensor FROM sensor WHERE id_sensor="+id_sensor+";");  
		        if(resultSet.next()){
		           i=resultSet.getInt(1);
		           ResultSet rs= statement  
			                 .executeQuery("SELECT estado FROM sensor WHERE id_sensor="+id_sensor+";"); 
		           b=rs.getBoolean(1);
		        }
		           System.out.println("id_sensor:"+id_sensor);
		           System.out.println("i:"+i);
		           System.out.println("b:"+b);
		         if((i==id_sensor)&&(b)){
		        	Date d=new Date(System.currentTimeMillis());
		     		Time t=new Time(d.getTime());
		     		//Conexion.insertarMedida(1, d, t, 1, 5.0f);
		     		resultSet = statement  
			                 .executeQuery("SELECT * FROM coordenada WHERE id_coord=1;");
		     		String latitud=resultSet.getString(2);
		     		String longitud=resultSet.getString(3);
		     		Medida m=new Medida(d, t, latitud, longitud, 5.0f);
		     		o=m;
		     		resultSet.close();
		     		Conexion.insertarMedida(1, d, t, 1, 5.0f);
		         }else if((i==id_sensor)&&(!b)){
		        	 o=0;
		         }else if(i!=id_sensor){
		        	 o=-1;
		         }
		        
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		        	 
		           //  resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return o;
			/*Object o=new Object();
			Vector<Integer> a=new Vector<Integer>();
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT id_medida FROM medida WHERE id_sensor="+id_sensor+";");  
		         while (resultSet.next()) 
		         {  
		            int ar=resultSet.getInt(1);
		            a.add(ar);
		        	
		         }  
		         ResultSet rs = statement  
		                 .executeQuery("SELECT * FROM medida WHERE id_medida="+a.lastElement()+";"); 
		            Medida m=new Medida(rs.getDate(3), rs.getTime(4), "", "", rs.getFloat(6));
		            int id_coord=rs.getInt(5);
		            ResultSet rS = statement  
			                 .executeQuery("SELECT * FROM coordenada WHERE id_coord="+id_coord+";"); 	
		            m.setCoordenadas(rS.getString(2), rS.getString(3));
		            if(!resultSet.last()){
		            	o=-1;
		            }else{
		            ResultSet r=statement  
			                 .executeQuery("SELECT * FROM sensor WHERE id_sensor="+id_sensor+";"); 
		            if(r.getBoolean(3)==false){
		            	o=0;
		            }else{
		            	o=m;
		            }
		            }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     }
			return o; */
		}
		
		//Devuelve la url si existe en la bd
		//Devuelve un String vacio si no ex
		public static String getFoto(String id_vehiculo){
			String s=null;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado_gps FROM vehiculo WHERE id_vehiculo="+id_vehiculo.substring(9)+";");  
		        boolean est=resultSet.getBoolean(1);
		        if(est){
		        	ResultSet rs=statement  
			                 .executeQuery("SELECT url FROM vehiculo WHERE id_vehiculo="+id_vehiculo.substring(9)+";");  
		        s=rs.getString(1);
		        }else if(!est){
		        	s="";
		        }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return s;
		}
		
		
		//METODOS SERVIDOR LOCALIZACIONES
		
		public static String getLoc(int id_vehiculo){
			String s=null;
			
			//System.out.println("login:"+login);
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerLocDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado_gps FROM vehiculo WHERE id_vehiculo="+id_vehiculo+";");  
		          //System.out.println("rs="+resultSet.getString(1));
		           if(resultSet.getBoolean(1)==false){
		        	   s="";
		           }else{
		        	   ResultSet rs= statement  
				                 .executeQuery("SELECT id_coord FROM sensor WHERE id_vehiculo="+id_vehiculo+";");  
		        	   ResultSet r=statement  
				                 .executeQuery("SELECT * FROM coordenada WHERE id_coord="+rs.getInt(1)+";");  
		        	   s=r.getString(2)+"-"+r.getString(3);
		          
		           }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
		
			return s;
			
		}
		
		public static String getCoord(){
			String s="";
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT * FROM coordenada WHERE id_coord=1;");  
		        
		            s=resultSet.getString(2)+" - "+resultSet.getString(3);
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return s;
		}
		
		//Devuelve true si el pass es correcto
		//Devuelve false si el pass es incorrecto
		public static boolean passLoc(String login,String pass){
			boolean b=false;
			
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerLocDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT clave FROM usuario WHERE nombre='"+login+"';");  
		          
		           if(resultSet.getString(1).equals(pass)){
		        	   b=true;
		           }else{
		        	   b=false;
		           }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return b;
		}
		
		//Devuelve null si la celda es desconocida
		//Devuelve el String de latitud-longitud correspondiente.
		public static String getCoor(int id_celda){
			String s=null;
			int id_coord;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerLocDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT id_coord FROM celda WHERE id_celda="+id_celda+";");  
		         
		         if(!resultSet.next()){
			        	s="";
			        }else{
		          
		            id_coord=resultSet.getInt(1);
		         
			      ResultSet rs= statement  
			                 .executeQuery("SELECT * FROM coordenada WHERE id_coord="+id_coord+";");
		        s=rs.getString(2)+" - "+rs.getString(3);
			        }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  

		        	 resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return s;
		}
		
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}

